# coding:utf-8
from scrapyExercise import settings
from dbutils.pooled_db import PooledDB
import logging, threading, pymysql


class MysqlUtil(object):
    """
    @author Hongv
    @desc mysql工具类
    """

    _instance_lock = threading.Lock()

    def __init__(self):
        # 数据库构造函数，从连接池中取出连接，并生成操作游标
        self._connection = self._pool.connection()
        self._cursor = self._connection.cursor()

    def __new__(cls, *args, **kwargs):
        if not hasattr(MysqlUtil, "_instance"):
            with MysqlUtil._instance_lock:
                if not hasattr(MysqlUtil, "_instance"):
                    MysqlUtil._pool = PooledDB(creator=pymysql, mincached=1, maxcached=10,
                                                host=settings.MYSQL_HOST, port=settings.MYSQL_PORT,
                                                user=settings.MYSQL_USER, passwd=settings.MYSQL_PWD,
                                                db=settings.MYSQL_NAME, charset='utf8mb4')
                    MysqlUtil._instance = object.__new__(cls)
        return MysqlUtil._instance

    def get_all(self, sql, param=None):
        # 查询所有数据
        try:
            if param is None:
                count = self._cursor.execute(sql)
            else:
                count = self._cursor.execute(sql, param)
            if count > 0:
                result = self._cursor.fetchall()
            else:
                result = []
            return result
        except Exception as e:
            raise e

    def get_one(self, sql, param=None):
        # 查询某一个数据
        try:
            if param is None:
                count = self._cursor.execute(sql)
            else:
                count = self._cursor.execute(sql, param)
            if count > 0:
                result = self._cursor.fetchone()
            else:
                result = False
            return result
        except Exception as e:
            raise e

    def get_count(self, sql, param=None):
        # 查询数量
        try:
            if param is None:
                count = self._cursor.execute(sql)
            else:
                count = self._cursor.execute(sql, param)
            return count
        except Exception as e:
            raise e

    def get_many(self, sql, num, param=None):
        # 查询部分
        try:
            if param is None:
                count = self._cursor.execute(sql)
            else:
                count = self._cursor.execute(sql, param)
            if count > 0:
                result = self._cursor.fetchmany(num)
            else:
                result = False
            return result
        except Exception as e:
            raise e

    def insert_one(self, sql, value):
        # 插入一条数据
        try:
            row_count = self._cursor.execute(sql, value)
            return row_count
        except Exception as e:
            logging.exception(e)
            self.end("rollback")

    def insert_many(self, sql, values):
        # 插入多条数据
        try:
            row_count = self._cursor.executemany(sql, values)
            return row_count
        except Exception as e:
            logging.exception(e)
            self.end("rollback")

    def __query(self, sql, param=None):
        # 执行sql
        try:
            if param is None:
                count = self._cursor.execute(sql)
            else:
                count = self._cursor.execute(sql, param)
            return count
        except Exception as e:
            raise e

    def update(self, sql, param=None):
        # 更新
        return self.__query(sql, param)

    def insert(self, sql, param=None):
        # 更新
        return self.__query(sql, param)

    def delete(self, sql, param=None):
        # 删除
        return self.__query(sql, param)

    def begin(self):
        self._connection.autocommit(0)

    def end(self, option='commit'):
        if option == 'commit':
            self._connection.commit()
        else:
            self._connection.rollback()

    def dispose(self, is_end=1):
        if is_end == 1:
            self.end(option='commit')
        else:
            self.end(option='rollback')
        self._cursor.close()
        self._connection.close()